package cn.myschool.java79.dao;

import cn.myschool.java79.entity.Student;
import cn.myschool.java79.util.DBUtil;
import cn.myschool.java79.util.StrUtil;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class StudentDao {

//    //查看全部学生表
//    public List<Student> stuAll(){
//        List<Student> list =new ArrayList<>();
//        Connection conn =null;
//        PreparedStatement ps =null;
//        ResultSet rs =null;
//        String sql ="select * from s_student";
//        try {
//            conn = DBUtil.getConnection();
//            ps=conn.prepareStatement(sql);
//            rs=ps.executeQuery();
//            while(rs.next()){
//                Student st =new Student();
//                st.setStuno(rs.getLong("stuno"));
//                st.setSname(rs.getString("sname"));
//                st.setSex(rs.getString("sex"));
//                st.setTelephone(rs.getString("telephone"));
//                st.setQq(rs.getString("qq"));
//                st.setWechat(rs.getString("wechat"));
//                st.setGradeid(rs.getLong("gradeid"));
//                st.setEnterdate(rs.getDate("enterdate"));
//                st.setFromcity(rs.getString("fromcity"));
//                st.setBirthday(rs.getDate("birthday"));
//                st.setAddress(rs.getString("address"));
//                st.setPass(rs.getString("pass"));
//                st.setIdcardpic(rs.getString("idcardpic"));
//                list.add(st);
//            }
//        } catch (SQLException e) {
//            e.printStackTrace();
//        }
//        finally {
//            DBUtil.close(rs,ps,conn);
//        }
//        return list;
//    }

    //增加
    public int add(Student s) {
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            conn = DBUtil.getConnection();
            String sql = "INSERT INTO `s_student` (`sname`, `sex`, `telephone`, `qq`, `wechat`, `gradeid`, `enterdate`, `fromcity`, `birthday`, `address`, `pass`, `idcardpic`) VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
            ps = conn.prepareStatement(sql);
            ps.setString(1, s.getSname());
            ps.setString(2, s.getSex());
            ps.setString(3, s.getTelephone());
            ps.setString(4, s.getQq());
            ps.setString(5, s.getWechat());
            ps.setLong(6, s.getGradeid());
            ps.setDate(7, (Date) s.getEnterdate());
            ps.setString(8, s.getFromcity());
            ps.setDate(9, (Date) s.getBirthday());
            ps.setString(10, s.getAddress());
            ps.setString(11, s.getPass());
            ps.setString(12, s.getIdcardpic());
            int num = ps.executeUpdate();
            return num;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(ps);
            DBUtil.close(conn);
        }
        return 0;
    }

    //修改
    public int update(Student s) {
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            conn = DBUtil.getConnection();
            String sql = "UPDATE `s_student` SET `sname` = ?, `sex` = ?, `telephone` = ?, `qq` = ?, `wechat` = ?, `gradeid` = ?, `enterdate` = ?, `fromcity` = ?, `birthday` = ?, `address` = ?, `pass` = ?, `idcardpic` = ? WHERE `stuno` = ?";
            ps = conn.prepareStatement(sql);
            ps.setString(1, s.getSname());
            ps.setString(2, s.getSex());
            ps.setString(3, s.getTelephone());
            ps.setString(4, s.getQq());
            ps.setString(5, s.getWechat());
            ps.setLong(6, s.getGradeid());
            ps.setDate(7, (Date) s.getEnterdate());
            ps.setString(8, s.getFromcity());
            ps.setDate(9, (Date) s.getBirthday());
            ps.setString(10, s.getAddress());
            ps.setString(11, s.getPass());
            ps.setString(12, s.getIdcardpic());
            ps.setLong(13, s.getStuno());
            int num = ps.executeUpdate();
            return num;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(ps);
            DBUtil.close(conn);
        }
        return 0;
    }

    //删除
    public int del(long stuno) {
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            conn = DBUtil.getConnection();
            String sql = "delete from s_student where stuno=?";
            ps = conn.prepareStatement(sql);
            ps.setLong(1, stuno);
            int i = ps.executeUpdate();
            return i;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(ps);
            DBUtil.close(conn);
        }
        return 0;
    }

    //查询单个学生
    public Student get(long stuno) {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = DBUtil.getConnection();
            String sql = "select * from `s_student` where `stuno` = ?";
            ps = conn.prepareStatement(sql);
            ps.setLong(1, stuno);
            rs = ps.executeQuery();
            while (rs.next()) {
                Student st = new Student();
                st.setStuno(rs.getLong("stuno"));
                st.setSname(rs.getString("sname"));
                st.setSex(rs.getString("sex"));
                st.setTelephone(rs.getString("telephone"));
                st.setQq(rs.getString("qq"));
                st.setWechat(rs.getString("wechat"));
                st.setGradeid(rs.getLong("gradeid"));
                st.setEnterdate(rs.getDate("enterdate"));
                st.setFromcity(rs.getString("fromcity"));
                st.setBirthday(rs.getDate("birthday"));
                st.setAddress(rs.getString("address"));
                st.setPass(rs.getString("pass"));
                st.setIdcardpic(rs.getString("idcardpic"));
                return st;
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(rs, ps, conn);
        }
        return null;
    }

    //分页查询
    public List<Student> limitStu(int pangNo, int pangSize) {
        List<Student> list = new ArrayList<>();
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = DBUtil.getConnection();
            String sql = "select * from s_student order by stuno desc limit ?,?";
            ps = conn.prepareStatement(sql);
            ps.setInt(1, (pangNo - 1) * pangSize);
            ps.setInt(2, pangSize);
            rs = ps.executeQuery();
            while (rs.next()) {
                Student st = new Student();
                st.setStuno(rs.getLong("stuno"));
                st.setSname(rs.getString("sname"));
                st.setSex(rs.getString("sex"));
                st.setTelephone(rs.getString("telephone"));
                st.setQq(rs.getString("qq"));
                st.setWechat(rs.getString("wechat"));
                st.setGradeid(rs.getLong("gradeid"));
                st.setEnterdate(rs.getDate("enterdate"));
                st.setFromcity(rs.getString("fromcity"));
                st.setBirthday(rs.getDate("birthday"));
                st.setAddress(rs.getString("address"));
                st.setPass(rs.getString("pass"));
                st.setIdcardpic(rs.getString("idcardpic"));
                list.add(st);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }


    /**
     * 模糊查询
     * @param pageNo
     * @param pageSize
     * @param sname
     * @param qq
     * @param phone
     * @param enterStart 入学时间 大于等于这个值
     * @param enterEnd 入学时间小于等于这个值
     * @return
     */
    public List<Student> search(int pageNo, int pageSize, String sname, String qq, String phone, java.util.Date enterStart,java.util.Date enterEnd) {
        List<Student> datas = new ArrayList<>();
        Connection connection = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        List<Object> params = new ArrayList<>();
        String sql = "select * from s_student where ";
        sql += "  2>1";
        if (!StrUtil.isBlank(sname)) {
            sql += " and sname like concat('%',?,'%')";
            params.add(sname);
        }
        if (!StrUtil.isBlank(qq)) {
            sql += " and qq like concat('%',?,'%')";
            params.add(qq);
        }

        if (!StrUtil.isBlank(phone)) {
            sql += " and telephone like concat('%',?,'%')";
            params.add(phone);
        }
        if (!StrUtil.isBlank(enterStart)) {
            sql += " and enterdate >= ?";
            params.add(enterStart);
        }
        if (!StrUtil.isBlank(enterEnd)) {
            sql += " and  enterdate<= ?";
            params.add(enterEnd);
        }

        sql += " limit ?,?";
        pageNo = pageNo < 1 ? 1 : pageNo;//防止他小于1
        params.add((pageNo - 1) * pageSize);
        params.add(pageSize);
        System.out.println(sql + "," + params);
        try {
            connection = DBUtil.getConnection();
            ps = connection.prepareStatement(sql);
            for (int i = 0; i < params.size(); i++) {
                ps.setObject(i + 1, params.get(i));
            }
            rs = ps.executeQuery();
            while (rs.next()) {
                Student student = new Student();
                student.setStuno(rs.getInt("stuno"));
                student.setSname(rs.getString("sname"));
                student.setSex(rs.getString("sex"));
                student.setTelephone(rs.getString("telephone"));
                student.setQq(rs.getString("qq"));
                student.setWechat(rs.getString("wechat"));
                student.setGradeid(rs.getInt("gradeid"));
                student.setEnterdate(rs.getDate("enterdate"));
                student.setBirthday(rs.getDate("birthday"));
                student.setFromcity(rs.getString("fromcity"));
                student.setAddress(rs.getString("address"));
                student.setPass(rs.getString("pass"));
                datas.add(student);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(rs, ps, connection);
        }
        return datas;
    }
//    public List<Student> search(int pageNo, int pageSize, String sname, String qq, String phone, Date enterStart, Date enterEnd) {
//        List<Student> datas = new ArrayList<>();
//        Connection connection = null;
//        PreparedStatement ps = null;
//        ResultSet rs = null;
//        List<Object> params = new ArrayList<>();
//        String sql = "select * from s_student where ";
//        sql += "  2>1";
//        if (!StrUtil.isBlank(sname))
//            sql += " and sname like concat('%',?,'%')";
//        if (!StrUtil.isBlank(qq))
//            sql += " and qq like concat('%',?,'%')";
//
//        if (!StrUtil.isBlank(phone))
//            sql += " and telephone like concat('%',?,'%')";
//
//
//        sql += " limit ?,?";
//        System.out.println(sql);
//        try {
//            connection = DBUtil.getConnection();
//            ps = connection.prepareStatement(sql);
//            int index = 1;
//            if (!StrUtil.isBlank(sname))
//                ps.setString(index++, sname);
//            if (!StrUtil.isBlank(qq))
//                ps.setString(index++, qq);
//            if (!StrUtil.isBlank(phone))
//                ps.setString(index++, phone);
//            ps.setInt(index++, (pageNo - 1) * pageSize);
//            ps.setInt(index++, pageSize);
//
//            rs = ps.executeQuery();
//            while (rs.next()) {
//                Student student = new Student();
//                student.setStuno(rs.getInt("stuno"));
//                student.setSname(rs.getString("sname"));
//                student.setSex(rs.getString("sex"));
//                student.setTelephone(rs.getString("telephone"));
//                student.setQq(rs.getString("qq"));
//                student.setWechat(rs.getString("wechat"));
//                student.setGradeid(rs.getInt("gradeid"));
//                student.setEnterdate(rs.getDate("enterdate"));
//                student.setBirthday(rs.getDate("birthday"));
//                student.setFromcity(rs.getString("fromcity"));
//                student.setAddress(rs.getString("address"));
//                student.setPass(rs.getString("pass"));
//                datas.add(student);
//            }
//        } catch (SQLException e) {
//            e.printStackTrace();
//        } finally {
//            DBUtil.close(rs, ps, connection);
//        }
//        return datas;
//    }

    public int searchTotal(String sname, String qq, String phone, String addr, Long gradeid, Date enterStart, Date enterEnd) {
        Connection connection = null;
        int count = 0;
        PreparedStatement ps = null;
        ResultSet rs = null;
        List<Object> params = new ArrayList<>();
        String sql = "select * from s_student where 1=1";
        if (sname != null && !sname.equals("")) {
            sql += " and sname like concat('%',?,'%')";
            params.add(sname);
        }
        if (qq != null && !qq.equals("")) {
            sql += " and qq like concat('%',?,'%')";
            params.add(qq);
        }
        if (phone != null && !phone.equals("")) {
            sql += " and telephone like concat('%',?,'%')";
            params.add(phone);
        }
        if (addr != null && !addr.equals("")) {
            sql += " and address like concat('%',?,'%')";
            params.add(addr);
        }
        if (gradeid != null && !gradeid.equals("")) {
            sql += " and gradeid like concat('%',?,'%')";
            params.add(gradeid);
        }
        if (enterStart != null) {
            sql += "and enterdate > ?";
            params.add(enterStart);
        }
        if (enterEnd != null) {
            sql += "and enterdate < ?";
            params.add(enterEnd);
        }
        System.out.println(sql);
        try {
            connection = DBUtil.getConnection();
            ps = connection.prepareStatement(sql);
            for (int i = 1; i <= params.size(); i++) {
                ps.setObject(i, params.get(i - 1));
            }

            rs = ps.executeQuery();
            while (rs.next()) {
                count++;
            }
            return count;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(rs, ps, connection);
        }
        return 0;
    }

}
